/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package mybean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.Cookie;

/**
 *
 * @author Phu
 */
public class DBProcess {

    Connection conn;
    Statement stmt;

    public DBProcess() {
        try {
            conn = ConnectDBFactory.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Book> listBook(int catID) {
        List<Book> list = new ArrayList<Book>();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "";
            if (catID != 0) {
                SQL = "SELECT * FROM Book WHERE CatID=" + catID;
            } else {
                SQL = "SELECT * FROM Book";
            }
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                list.add(new Book(rs.getString("BookID"), rs.getString("BookTitle"), rs.getString("Author"), rs.getString("Publisher"), rs.getFloat("Price"), rs.getInt("catID"), rs.getString("Cover"), rs.getString("Summary"), rs.getString("Details")));
            }            
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public Book getBook(String id) {

        Book book = new Book();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "SELECT * FROM Book WHERE BookID='" + id + "'";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                book = new Book(rs.getString("BookID"), rs.getString("BookTitle"), rs.getString("Author"), rs.getString("Publisher"), rs.getFloat("Price"), rs.getInt("CatID"), rs.getString("Cover"), rs.getString("Summary"), rs.getString("Details"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return book;
    }

    public List<Category> listCategory() {
        List<Category> list = new ArrayList<Category>();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "SELECT * FROM Category";
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                list.add(new Category(rs.getInt("CatID"), rs.getString("CatName")));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
        
    public Customer getCustomer(String customerName){
        if("".equals(customerName) || customerName == null){
            return null;
        }
        try {
            String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=BookStore;user=sa;password=sa";
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection con = DriverManager.getConnection(connectionUrl);

            String SQL = "SELECT * FROM Customer WHERE name='" + customerName + "'";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
                        
            while(rs.next()){
                 String email = rs.getString("email");                 
                 String phone = rs.getString("phone");                 
                 String address = rs.getString("address");                 
                 String company = rs.getString("company");
                 String DoB = rs.getString("DoB");
                 if(company == null){
                     company = "Not available";
                 }
                 if(DoB == null){
                     DoB = "Not available";
                 }
                 Customer customer = new Customer();
                 customer.setCustomer(customerName, email, phone, address, company, DoB);
                 return customer;
            }
            return null;
        } catch (ClassNotFoundException ex) {            
            return null;
        } catch (SQLException ex) {            
            return null;
        }
    }
    
    public List<Book> search(String keyword, String searchfor) {
        List<Book> list = new ArrayList<Book>();
        try {
            conn = ConnectDBFactory.getConnection();
            String SQL = "SELECT * FROM Book WHERE " + searchfor + " LIKE '%" + keyword + "%'";           

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                list.add(new Book(rs.getString("BookID"), rs.getString("BookTitle"), rs.getString("Author"), rs.getString("Publisher"), rs.getFloat("Price"), rs.getInt("catID"), rs.getString("Cover"), rs.getString("Summary"), rs.getString("Details")));
            }            
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }
}
